#!/bin/bash
PATH="/opt/java/jdk1.8.0_151/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin"
echo "======================to do transform=========================="
cd docker-hive
echo "transform in background"
echo "======================== part 1 =========================="
docker-compose exec -d hive-server env LANG=C.UTF-8 bash /opt/hive/bin/beeline -u jdbc:hive2://localhost:10000 -e "insert overwrite table ads_wms.record_in_out_info
select statistical_type, 
statistical_time,
goods_count
from (select 'recent_day_total_in' statistical_type, 
substr(cast(in_time as string), 1, 10) statistical_time, 
sum(in_count) goods_count
from ods_wms.record_in
where substr(cast(in_time as string), 1, 10) < substr(cast(from_utc_timestamp(CURRENT_TIMESTAMP,'GMT+8')  as string), 1, 10)
and substr(cast(in_time as string), 1, 10) > substr(cast(date_sub(from_utc_timestamp(CURRENT_TIMESTAMP,'GMT+8') , 7) as string), 1, 10)
group by substr(cast(in_time as string), 1, 10)) t
union all
select statistical_type, 
statistical_time,
goods_count
from (select 'recent_month_total_in' statistical_type, 
substr(cast(in_time as string), 1, 7) statistical_time, 
sum(in_count) goods_count
from ods_wms.record_in
where substr(cast(in_time as string), 1, 7) < substr(cast(from_utc_timestamp(CURRENT_TIMESTAMP,'GMT+8')  as string), 1, 7)
and substr(cast(in_time as string), 1, 7) >= substr(cast(add_months(from_utc_timestamp(CURRENT_TIMESTAMP,'GMT+8') , -7) as string), 1, 7)
group by substr(cast(in_time as string), 1, 7)) t
union all
select statistical_type, 
statistical_time,
goods_count
from (select 'recent_year_total_in' statistical_type, 
cast(year(in_time) as string) statistical_time, 
sum(in_count) goods_count
from ods_wms.record_in
where year(in_time) < year(from_utc_timestamp(CURRENT_TIMESTAMP,'GMT+8') )
and year(in_time) >= year(from_utc_timestamp(CURRENT_TIMESTAMP,'GMT+8') ) - 7
group by year(in_time)) t
union all
select statistical_type, 
statistical_time,
goods_count
from (select 'recent_day_total_out' statistical_type, 
substr(cast(out_time as string), 1, 10) statistical_time, 
sum(out_count) goods_count
from ods_wms.record_out
where substr(cast(out_time as string), 1, 10) < substr(cast(from_utc_timestamp(CURRENT_TIMESTAMP,'GMT+8')  as string), 1, 10)
and substr(cast(out_time as string), 1, 10) >= substr(cast(date_sub(from_utc_timestamp(CURRENT_TIMESTAMP,'GMT+8') , 7) as string), 1, 10)
group by substr(cast(out_time as string), 1, 10)) t
union all
select statistical_type, 
statistical_time,
goods_count
from (select 'recent_month_total_out' statistical_type, 
substr(cast(out_time as string), 1, 7) statistical_time, 
sum(out_count) goods_count
from ods_wms.record_out
where substr(cast(out_time as string), 1, 7) < substr(cast(from_utc_timestamp(CURRENT_TIMESTAMP,'GMT+8')  as string), 1, 7)
and substr(cast(out_time as string), 1, 7) >= substr(cast(add_months(from_utc_timestamp(CURRENT_TIMESTAMP,'GMT+8') , -7) as string), 1, 7)
group by substr(cast(out_time as string), 1, 7)) t
union all
select statistical_type, 
statistical_time,
goods_count
from (select 'recent_year_total_out' statistical_type, 
cast(year(out_time) as string) statistical_time, 
sum(out_count) goods_count
from ods_wms.record_out
where year(out_time) < year(from_utc_timestamp(CURRENT_TIMESTAMP,'GMT+8') )
and year(out_time) >= year(from_utc_timestamp(CURRENT_TIMESTAMP,'GMT+8') ) - 7
group by year(out_time)) t;"
